/**
   Creator : Thinh Nguyen
   Created Date: 23/2/2013
   Modified Date: 24/2/2013
**/
CREATE DATABASE ExpressBookingSystem_THPN
DROP DATABASE ExpressBookingSystem_THPN
USE ExpressBookingSystem_THPN

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserProfile]') AND
		TYPE IN (N'U'))
		DROP TABLE [dbo].[UserProfile]
/** Create User profile **/
CREATE TABLE [UserProfile]
(
	UserId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
	FirstName NVARCHAR(50) NOT NULL,
	LastName NVARCHAR(50) NOT NULL,
	PhoneNumber VARCHAR(50) NOT NULL,
	Email NVARCHAR(128) NOT NULL,
	[Address] NVARCHAR(128) NULL,
	City NVARCHAR(50) NULL,
	[State] NVARCHAR(50) NULL,
	Country NVARCHAR(50) NULL,
	Deleted BIT NOT NULL DEFAULT(0)
)
CREATE INDEX idxUserProfile ON dbo.UserProfile (FirstName, LastName)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserRole]') AND
		TYPE IN (N'U'))
		DROP TABLE [dbo].[UserRole]
/** Create UserRole table **/
CREATE TABLE [UserRole]
(
	RoleId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	RoleName NVARCHAR(256) NOT NULL,
	[Description] NVARCHAR(256) NOT NULL,
	Deleted BIT NOT NULL DEFAULT(0)
)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[User]') AND
		TYPE IN (N'U'))
		DROP TABLE [dbo].[User]
/** Create User table **/
CREATE TABLE [User]
(
	UserId UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
	Username VARCHAR(50) NOT NULL,
	[Password] NVARCHAR(128) NOT NULL,
	PasswordQuestion NVARCHAR(256) NULL,
	PassswordAnswer NVARCHAR(128) NULL,
	IsApproved BIT NOT NULL,
	IsLocked BIT NOT NULL,
	CreatedDate DateTime NOT NULL,
	LastLoginDate Datetime NOT NULL,
	LastPasswordChangedDate Datetime NOT NULL,	
	RoleId INT FOREIGN KEY REFERENCES [dbo].[UserRole](RoleId),
	Deleted BIT NOT NULL DEFAULT(0)
)
CREATE INDEX idxUser ON [dbo].[User] (Username)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Location]') AND
		TYPE IN (N'U'))
		DROP TABLE [dbo].[Location]
CREATE TABLE [Location]
(
	LocationId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	LocationName NVARCHAR(128) NOT NULL,
	Latitude DECIMAL(18,15) NULL,
	Longitude DECIMAL(18,15) NULL,
	[Description] NVARCHAR(256) NULL,
	Deleted bit NOT NULL DEFAULT (0)
)


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BusPort]') AND
		TYPE IN (N'U'))
		DROP TABLE [dbo].[BusPort]
/** Create bus port table **/
CREATE TABLE [BusPort]
(
	BusPortId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Name NVARCHAR(128) NOT NULL,
	LocationId INT FOREIGN KEY REFERENCES [dbo].[Location](LocationId),	
	Deleted BIT NOT NULL DEFAULT(0)
)
CREATE INDEX idxBusPortName ON [dbo].[BusPort](Name)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BusType]') AND
		TYPE IN (N'U'))
		DROP TABLE [dbo].[BusType]
/** Create BusType table **/
CREATE TABLE [BusType]
(
	BusTypeId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Name NVARCHAR(128) NOT NULL,
	SeatQuantity INT NOT NULL,
	Price FLOAT NOT NULL,
	Deleted BIT NOT NULL DEFAULT(0)
)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TripSchedule]') AND
		TYPE IN (N'U'))
		DROP TABLE [dbo].[TripSchedule]
/** Create TripSchedule table **/
CREATE TABLE [TripSchedule]
(
	TripScheduleId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	DepartureDate DATETIME NOT NULL,
	ArrivalDate DATETIME NOT NULL,
	DeparturePortId INT FOREIGN KEY REFERENCES [dbo].[BusPort](BusPortId),
	ArrivalPortId INT FOREIGN KEY REFERENCES [dbo].[BusPort](BusPortId),
	Price FLOAT NOT NULL,
	Deleted BIT NOT NULL
)

CREATE INDEX idxDateTripSchedule ON [dbo].[TripSchedule]  (DepartureDate, ArrivalDate)
CREATE INDEX idxBusPort ON [dbo].[TripSchedule]  (DeparturePortId, ArrivalPortId)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ActualTrip]') AND
		TYPE IN (N'U'))
		DROP TABLE [dbo].[ActualTrip]
/** Create ActualTrip table **/
CREATE TABLE [ActualTrip]
(
	ActualTripId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	TripScheduleId INT FOREIGN KEY REFERENCES [dbo].[TripSchedule](TripScheduleId),
	ActualDepDate DATETIME NULL,
	ActualArrivalDate DATETIME NULL,
	ActualPrice FLOAT NOT NULL,
	BookedSeatQuantity TINYINT NOT NULL,
	BusTypeId INT FOREIGN KEY REFERENCES [dbo].[BusType](BusTypeId)
)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Booking]') AND
		TYPE IN (N'U'))
		DROP TABLE [dbo].[Booking]
/** Create Booking table **/
CREATE TABLE [Booking]
(
	BookingId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	UserId UNIQUEIDENTIFIER FOREIGN KEY REFERENCES [dbo].[User],
	BookedDate DATETIME NOT NULL,
	PaymentRecieved BIT NOT NULL,
	ConfirmedDate DATETIME NULL,
	Total FLOAT NOT NULL,
	Quantity TINYINT NOT NULL,
	Deleted BIT NOT NULL DEFAULT(0)
)

CREATE INDEX idxUserBooked ON [dbo].[Booking]  (UserId)
CREATE INDEX idxBookedDate ON [dbo].[Booking]  (BookedDate)

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BookingDetail]') AND
		TYPE IN (N'U'))
		DROP TABLE [dbo].[BookingDetail]
/** Create BookingDetail table **/
CREATE TABLE [BookingDetail]
(
	BookingDetailId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
	CreatedDate DATETIME NOT NULL,
	ActualTripId INT FOREIGN KEY REFERENCES [dbo].[ActualTrip](ActualTripId),
	BookingId INT FOREIGN KEY REFERENCES [dbo].[Booking],
	IsBooked BIT NOT NULL,
	SeatNumber TINYINT NOT NULL,
	Deleted BIT NOT NULL
)
CREATE INDEX idxBooking ON [dbo].[BookingDetail] (BookingId)
CREATE INDEX idxBookedTrip ON [dbo].[BookingDetail] (ActualTripId)


/*************************************************************************/
/** Data **/
/*************************************************************************/
DECLARE @UserId uniqueidentifier
SET @UserId = NEWID()
INSERT INTO UserRole VALUES ('Customer', '', 0)
INSERT INTO UserProfile VALUES (@UserId, 'Thinh', 'Nguyen', '0969933938', 'gaupoit@gmail.com', '77 bis 3 thang 2', 'Dalat', 'P4', 'Vietnam', 0)
INSERT INTO [User] VALUES (@UserId, 'gaupoit', '096993938', '', '', 1, 0, '2/24/2013', '2/24/2013', '2/24/2013', 1, 0)

DECLARE @UserId2 uniqueidentifier
SET @UserId2 = NEWID()
INSERT INTO UserRole VALUES ('Admin', '', 0)
INSERT INTO UserProfile VALUES (@UserId2, 'Hung', 'Dang', '0969933938', 'gaupoit@gmail.com', '77 bis 3 thang 2', 'Dalat', 'P4', 'Vietnam', 0)
DECLARE @RoleId INT
SELECT @RoleId = RoleId  FROM UserRole 
INSERT INTO [User] VALUES (@UserId2, 'hungdd', '096993938', '', '', 1, 0, '2/24/2013', '2/24/2013', '2/24/2013', @RoleId, 0)

INSERT INTO Location(LocationName, Deleted) VALUES ('Da lat', 0)
INSERT INTO Location(LocationName, Deleted) VALUES ('Da Nang', 0)
INSERT INTO Location(LocationName, Deleted) VALUES ('Sai gon', 0)

DECLARE @locationId1 INT 
SELECT @locationId1 = LocationId FROM Location WHERE LocationName = 'Dalat'
DECLARE @locationId2 INT 
SELECT @locationId2 = LocationId FROM Location WHERE LocationName = 'Da Nang'
INSERT INTO BusPort VALUES ('Dalat - Danang', @locationId1, 0)
INSERT INTO BusPort VALUES ('Dalat - Danang', @locationId2, 0)

DECLARE @DeparturePortId1 INT
DECLARE @DeparturePortId2 INT
SELECT @DeparturePortId1 = BusPortId FROM BusPort b INNER JOIN Location l ON b.LocationId = l.LocationId 
			WHERE Name = 'Dalat - Danang' AND l.LocationName = 'Dalat'
SELECT @DeparturePortId2 = BusPortId FROM BusPort b INNER JOIN Location l ON b.LocationId = l.LocationId 
			WHERE Name = 'Dalat - Danang' AND l.LocationName = 'Da Nang'
INSERT INTO TripSchedule (DepartureDate, ArrivalDate, DeparturePortId, ArrivalPortId, Price, Deleted)
		VALUES ('2/24/2013', '2/25/2012',@DeparturePortId1, @DeparturePortId2, 240000, 0)

INSERT INTO BusType(Name, SeatQuantity, Price, Deleted) VALUES ('Ghe', 24, 240000, 0) 
INSERT INTO BusType(Name, SeatQuantity, Price, Deleted) VALUES ('Giuong', 48, 240000, 0)

INSERT INTO ActualTrip(TripScheduleId, ActualPrice, BookedSeatQuantity, BusTypeId)
		VALUES (1, 250000, 12, 1) 

DECLARE @gaupoitId uniqueidentifier
SELECT @gaupoitId = UserId from [User] WHERE Username = 'gaupoit'
INSERT INTO Booking VALUES (@gaupoitId, '2/24/2013', 0, NULL, 240000, 1, 0)

INSERT INTO [dbo].[BookingDetail] VALUES('2/24/2012', 1, 1, 0, 2, 0)
